package Server.DataBase;

import java.sql.Blob;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.sql.rowset.serial.SerialBlob;

import Client.logic.MyFile;
 /**
 * implement all TaskQuery  handler
 * */

public class TaskQuery extends Query{
	
	public TaskQuery() {
		super();
	}
/**
 * add task to lesson
 * @param lesson
 * @param date
 * @param file
 * @throws SQLException
 */
	public void addTask(Lesson lesson, int[] date, MyFile file) throws SQLException{
		setQuery("INSERT INTO task(idtask, idLesson, file, filename, size, day, month, year) VALUES(?,?,?,?,?,?,?,?)");
		PreparedStatement query = getPS();
		query.setInt(1, getTaskId(lesson.getIdLesson()));
		query.setInt(2, lesson.getIdLesson());
		query.setBlob(3, file.getBlob());
		query.setString(4, file.getFileName());
		query.setInt(5, file.getSize());
		query.setInt(6,date[0]);
		query.setInt(7,date[1]);
		query.setInt(8,date[2]);
		query.executeUpdate();
	}
	
	public boolean isExistSubmitTask(int idTask, int idLesson, int idUser) throws SQLException{
		setQuery("SELECT idtask FROM submittask WHERE idTask='"+idTask+"' " +
				"AND idLesson='"+idLesson+"' AND iduser='"+idUser+"'");
		if (execQuery().next()) return true;
		return false;
	}
	
	 public void removeSubmitTask(int idTask, int idLesson, int idUser) throws SQLException{
     	setQuery("DELETE FROM submittask WHERE idTask=? " +
				"AND idLesson=? AND iduser=?");
     	PreparedStatement query = getPS();
     	query.setInt(1, idTask);
     	query.setInt(2, idLesson);
     	query.setInt(3, idUser);
     	query.executeUpdate();
     }
	/**
	 * submitted students task 
	 * @param idTask
	 * @param lesson
	 * @param student
	 * @param date
	 * @param file
	 * @return
	 * @throws SQLException
	 */
	public int SubmitTask(int idTask, int lesson, int student, int[] date, MyFile file) throws SQLException{
		if (isExistSubmitTask(idTask, lesson, student)) removeSubmitTask(idTask, lesson, student);
		setQuery("INSERT INTO submittask(idtask, idLesson, iduser, file, filename, size, day, month, year, ontime)" +
				" VALUES(?,?,?,?,?,?,?,?,?,?)");
		PreparedStatement query = getPS();
		int tmp = isOnTime(idTask, lesson, date);
		query.setInt(1, idTask);
		query.setInt(2, lesson);
		query.setInt(3, student);
		query.setBlob(4, file.getBlob());
		query.setString(5, file.getFileName());
		query.setInt(6, file.getSize());
		query.setInt(7,date[0]);
		query.setInt(8,date[1]);
		query.setInt(9,date[2]);
		query.setInt(10, tmp);
		query.executeUpdate();
		return tmp;
	}
	
	public boolean isExistGradeTask(int idTask, int idLesson, int idUser) throws SQLException{
		setQuery("SELECT idtask FROM gradetask WHERE idTask='"+idTask+"' " +
				"AND idLesson='"+idLesson+"' AND iduser='"+idUser+"'");
		if (execQuery().next()) return true;
		return false;
	}
	
	 public void removeGradeTask(int idTask, int idLesson, int idUser) throws SQLException{
     	setQuery("DELETE FROM gradetask WHERE idTask=? " +
				"AND idLesson=? AND iduser=?");
     	PreparedStatement query = getPS();
     	query.setInt(1, idTask);
     	query.setInt(2, idLesson);
     	query.setInt(3, idUser);
     	query.executeUpdate();
     }
	/**
	 * teacher check task and give grade
	 * @param idTask
	 * @param lesson
	 * @param student
	 * @param grade
	 * @param commant
	 * @param file
	 * @throws SQLException
	 */
	public void GradeTask(int idTask, int lesson, int student, int grade, String commant, MyFile file) throws SQLException{
		if (isExistGradeTask(idTask, lesson, student)) removeGradeTask(idTask, lesson, student);
		setQuery("INSERT INTO gradetask(idtask, idLesson, iduser, file, filename, size, grade, commant)" +
				" VALUES(?,?,?,?,?,?,?,?)");
		PreparedStatement query = getPS();
		query.setInt(1, idTask);
		query.setInt(2, lesson);
		query.setInt(3, student);
		query.setBlob(4, file.getBlob());
		query.setString(5, file.getFileName());
		query.setInt(6, file.getSize());
		query.setInt(7,grade);
		query.setString(8,commant);
		query.executeUpdate();
	}
	/**
	 * check task submitted is not pass date
	 * @param idTask
	 * @param idLesson
	 * @param date
	 * @return
	 * @throws SQLException
	 */
	public int isOnTime(int idTask, int idLesson, int[] date) throws SQLException{
		setQuery("SELECT day, month, year FROM task WHERE " +
				"idtask='"+idTask+"' AND idLesson='"+idLesson+"'");
		ResultSet rs = execQuery();
		if(rs.next()){
			if (date[2]==rs.getInt(3)){ 
				if (date[1]==rs.getInt(2)){
					if  (date[0]<=rs.getInt(1)) return 1;
				}
				else{
					if (date[1]<rs.getInt(2)) return 1;
				}
			}
			else if (date[2]<rs.getInt(3)) return 1;
		}
		return 0;
	}
	/**
	 * 
	 * @param idLesson
	 * @return idtask
	 * @throws SQLException
	 */
	public int getTaskId(int idLesson) throws SQLException{
		int max=1;
		setQuery("SELECT idtask FROM task WHERE idLesson='"+idLesson+"'");
		ResultSet rs = execQuery();
		while (rs.next()) max++;
		return max;
	}
	/**
	 * 
	 * @return task table
	 * @throws SQLException
	 */
	public ResultSet getTable() throws SQLException{
		setQuery("SELECT idtask, idlesson, date, filename FROM task");
		return execQuery();
	}
	/**
	 * 
	 * @param idLesson
	 * @param idTask
	 * @return
	 * @throws SQLException
	 */
	public MyFile getTaskFile(int idLesson, int idTask) throws SQLException{
		setQuery("SELECT * FROM task WHERE idtask='"+idTask+"' AND idLesson='"+idLesson+"'");
		ResultSet rs = execQuery();
		if (rs.next()){
			String str = rs.getString(4);
			int size = rs.getInt(5);
			Blob blob =  rs.getBlob(3);
			SerialBlob blob2 = new SerialBlob( blob.getBytes(1, size));
			return new MyFile(str, blob2, size); 
		}
		return null;
	}
	/**
	 * 
	 * @param idLesson
	 * @param idTask
	 * @param idUser
	 * @return
	 * @throws SQLException
	 */
	public MyFile getSubmittedFile(int idLesson, int idTask, int idUser) throws SQLException{
		setQuery("SELECT * FROM submittask WHERE idtask='"+idTask+"' AND idLesson='"+idLesson+"' " +
				"AND iduser='"+idUser+"'");
		ResultSet rs = execQuery();
		if (rs.next()){
			String str = rs.getString(5);
			int size = rs.getInt(6);
			Blob blob =  rs.getBlob(4);
			SerialBlob blob2 = new SerialBlob( blob.getBytes(1, size));
			return new MyFile(str, blob2, size); 
		}
		return null;
	}
	/**
	 * 
	 * @param idLesson
	 * @param idTask
	 * @param idUser
	 * @return
	 * @throws SQLException
	 */
	public MyFile getGradeFile(int idLesson, int idTask, int idUser) throws SQLException{
		setQuery("SELECT * FROM gradetask WHERE idtask='"+idTask+"' AND idLesson='"+idLesson+"' " +
				"AND iduser='"+idUser+"'");
		ResultSet rs = execQuery();
		if (rs.next()){
			String str = rs.getString(5);
			int size = rs.getInt(6);
			Blob blob =  rs.getBlob(4);
			SerialBlob blob2 = new SerialBlob( blob.getBytes(1, size));
			return new MyFile(str, blob2, size); 
		}
		return null;
	}
	/**
	 * 
	 * @param idTask
	 * @param idLesson
	 * @return
	 * @throws SQLException
	 */
	public ArrayList<SubmittedTask> getSubmittedToTask(int idTask, int idLesson) throws SQLException{
		ArrayList<SubmittedTask> array = new ArrayList<SubmittedTask>();
		setQuery("SELECT * FROM submittask WHERE idtask='"+idTask+"' AND idlesson='"+idLesson+"'");
		ResultSet rs = execQuery();
		while (rs.next()){
			Lesson lesson = new LessonQuery().getLesson(idLesson);
			Student student = new StudentQuery().getStudent(rs.getInt(3));
			array.add(new SubmittedTask(idTask, lesson,student,null,null,rs.getString(5),rs.getInt(10)));
		}
		return array;
	}
	/**
	 * getGradeTasks
	 * @param idUser
	 * @param idLesson
	 * @return
	 * @throws SQLException
	 */
	public ArrayList<GradeTask> getGradeTasks(int idUser, int idLesson) throws SQLException{
		ArrayList<GradeTask> array = new ArrayList<GradeTask>();
		setQuery("SELECT * FROM gradetask WHERE iduser='"+idUser+"' AND idlesson='"+idLesson+"'");
		ResultSet rs = execQuery();
		while (rs.next())
			array.add(new GradeTask(rs.getString(5), rs.getString(8), rs.getInt(7), rs.getInt(1), rs.getInt(2)));
		return array;
	}
	
	
}
